"""
Inception集成管理器
提供SQL审核、执行和回滚功能
"""

import pymysql
import json
import logging
import time
from typing import Dict, List, Tuple, Optional
from django.conf import settings
from django.utils import timezone
from .models import DatabaseConnection, SQLAuditRecord

logger = logging.getLogger(__name__)


class InceptionManager:
    """Inception管理器"""
    
    def __init__(self, connection: DatabaseConnection):
        self.connection = connection
        self.inception_host = connection.inception_host or 'localhost'
        self.inception_port = connection.inception_port or 6669
    
    def audit_sql(self, sql_content: str, user) -> Dict:
        """审核SQL"""
        try:
            # 如果启用了Inception，使用Inception审核
            if self.connection.use_inception and self.inception_host:
                return self._audit_with_inception(sql_content, user)
            else:
                # 使用内置审核规则
                return self._audit_with_builtin_rules(sql_content, user)
        except Exception as e:
            logger.error(f"SQL审核失败: {e}")
            return {
                'success': False,
                'error': str(e),
                'audit_record': None
            }
    
    def _audit_with_inception(self, sql_content: str, user) -> Dict:
        """使用Inception审核SQL"""
        try:
            # 连接Inception
            inception_conn = pymysql.connect(
                host=self.inception_host,
                port=self.inception_port,
                user='',
                password='',
                charset='utf8mb4'
            )
            
            cursor = inception_conn.cursor()
            
            # 构造Inception审核SQL
            inception_sql = f"""/*--user={self.connection.username};--password={self.connection.password};--host={self.connection.host};--port={self.connection.port};--check=1;*/
inception_magic_start;
use {self.connection.database};
{sql_content}
inception_magic_commit;"""
            
            cursor.execute(inception_sql)
            results = cursor.fetchall()
            
            # 解析Inception结果
            audit_result = []
            error_count = 0
            warning_count = 0
            max_error_level = 0
            
            for result in results:
                stage, error_level, stage_status, error_message, sql_text, affected_rows, backup_dbname = result
                
                audit_result.append({
                    'stage': stage,
                    'error_level': error_level,
                    'stage_status': stage_status,
                    'error_message': error_message,
                    'sql_text': sql_text,
                    'affected_rows': affected_rows,
                    'backup_dbname': backup_dbname
                })
                
                if error_level == 1:
                    warning_count += 1
                elif error_level == 2:
                    error_count += 1
                
                max_error_level = max(max_error_level, error_level)
            
            cursor.close()
            inception_conn.close()
            
            # 创建审核记录
            audit_record = SQLAuditRecord.objects.create(
                connection=self.connection,
                sql_content=sql_content,
                audit_result=json.dumps(audit_result, ensure_ascii=False),
                inception_result=audit_result,
                error_level=max_error_level,
                warning_count=warning_count,
                error_count=error_count,
                status='approved' if max_error_level < 2 else 'rejected',
                submitted_by=user
            )
            
            return {
                'success': True,
                'audit_record': audit_record,
                'results': audit_result,
                'error_level': max_error_level,
                'warning_count': warning_count,
                'error_count': error_count,
                'can_execute': max_error_level < 2
            }
            
        except Exception as e:
            logger.error(f"Inception审核失败: {e}")
            return {
                'success': False,
                'error': f"Inception审核失败: {str(e)}",
                'audit_record': None
            }
    
    def _audit_with_builtin_rules(self, sql_content: str, user) -> Dict:
        """使用内置规则审核SQL"""
        audit_result = []
        error_count = 0
        warning_count = 0
        max_error_level = 0
        
        # 基本SQL安全检查
        sql_upper = sql_content.upper().strip()
        
        # 危险操作检查
        dangerous_operations = [
            'DROP TABLE', 'DROP DATABASE', 'TRUNCATE TABLE',
            'DELETE FROM', 'UPDATE', 'ALTER TABLE'
        ]
        
        for operation in dangerous_operations:
            if operation in sql_upper:
                if operation in ['DROP TABLE', 'DROP DATABASE', 'TRUNCATE TABLE']:
                    error_count += 1
                    max_error_level = 2
                    audit_result.append({
                        'stage': 'SAFETY_CHECK',
                        'error_level': 2,
                        'stage_status': 'ERROR',
                        'error_message': f'检测到危险操作: {operation}',
                        'sql_text': sql_content[:100] + '...' if len(sql_content) > 100 else sql_content,
                        'affected_rows': 0,
                        'backup_dbname': ''
                    })
                else:
                    warning_count += 1
                    max_error_level = max(max_error_level, 1)
                    audit_result.append({
                        'stage': 'SAFETY_CHECK',
                        'error_level': 1,
                        'stage_status': 'WARNING',
                        'error_message': f'检测到需要谨慎操作: {operation}',
                        'sql_text': sql_content[:100] + '...' if len(sql_content) > 100 else sql_content,
                        'affected_rows': 0,
                        'backup_dbname': ''
                    })
        
        # 如果没有发现问题，添加通过记录
        if not audit_result:
            audit_result.append({
                'stage': 'SAFETY_CHECK',
                'error_level': 0,
                'stage_status': 'SUCCESS',
                'error_message': 'SQL语法检查通过',
                'sql_text': sql_content[:100] + '...' if len(sql_content) > 100 else sql_content,
                'affected_rows': 0,
                'backup_dbname': ''
            })
        
        # 创建审核记录
        audit_record = SQLAuditRecord.objects.create(
            connection=self.connection,
            sql_content=sql_content,
            audit_result=json.dumps(audit_result, ensure_ascii=False),
            error_level=max_error_level,
            warning_count=warning_count,
            error_count=error_count,
            status='approved' if max_error_level < 2 else 'rejected',
            submitted_by=user
        )
        
        return {
            'success': True,
            'audit_record': audit_record,
            'results': audit_result,
            'error_level': max_error_level,
            'warning_count': warning_count,
            'error_count': error_count,
            'can_execute': max_error_level < 2
        }
    
    def execute_sql(self, audit_record: SQLAuditRecord, user) -> Dict:
        """执行已审核的SQL"""
        if audit_record.status != 'approved':
            return {
                'success': False,
                'error': 'SQL未通过审核，无法执行'
            }
        
        try:
            # 连接目标数据库
            conn_params = self.connection.get_connection_params()
            conn = pymysql.connect(**conn_params)
            cursor = conn.cursor()
            
            # 执行SQL
            start_time = time.time()
            cursor.execute(audit_record.sql_content)
            execution_time = time.time() - start_time
            
            affected_rows = cursor.rowcount
            conn.commit()
            
            # 更新审核记录
            audit_record.status = 'executed'
            audit_record.executed_by = user
            audit_record.executed_at = timezone.now()
            audit_record.affected_rows = affected_rows
            audit_record.execution_time = execution_time
            audit_record.save()
            
            cursor.close()
            conn.close()
            
            return {
                'success': True,
                'affected_rows': affected_rows,
                'execution_time': execution_time,
                'audit_record': audit_record
            }
            
        except Exception as e:
            # 更新审核记录状态
            audit_record.status = 'failed'
            audit_record.executed_by = user
            audit_record.executed_at = timezone.now()
            audit_record.save()
            
            logger.error(f"SQL执行失败: {e}")
            return {
                'success': False,
                'error': str(e),
                'audit_record': audit_record
            }


def get_mysql_version(connection: DatabaseConnection) -> Optional[str]:
    """获取MySQL版本"""
    try:
        conn_params = connection.get_connection_params()
        conn = pymysql.connect(**conn_params)
        cursor = conn.cursor()
        cursor.execute("SELECT VERSION()")
        version = cursor.fetchone()[0]
        cursor.close()
        conn.close()
        return version
    except Exception as e:
        logger.error(f"获取MySQL版本失败: {e}")
        return None


def test_connection_compatibility(connection: DatabaseConnection) -> Dict:
    """测试数据库连接兼容性"""
    try:
        conn_params = connection.get_connection_params()
        conn = pymysql.connect(**conn_params)
        cursor = conn.cursor()
        
        # 获取版本信息
        cursor.execute("SELECT VERSION()")
        version = cursor.fetchone()[0]
        
        # 获取字符集信息
        cursor.execute("SHOW VARIABLES LIKE 'character_set%'")
        charset_info = dict(cursor.fetchall())
        
        # 获取排序规则信息
        cursor.execute("SHOW VARIABLES LIKE 'collation%'")
        collation_info = dict(cursor.fetchall())
        
        cursor.close()
        conn.close()
        
        return {
            'success': True,
            'version': version,
            'charset_info': charset_info,
            'collation_info': collation_info,
            'compatible': True
        }
        
    except Exception as e:
        return {
            'success': False,
            'error': str(e),
            'compatible': False
        }
